﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

using Fuse8.DomainFramework.Common;
using Fuse8.DomainFramework.Domain;
using Fuse8.DomainFramework.Common.Exceptions;

namespace Fuse8.DomainFramework.DataAccess.SqlDatabase
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    /// <typeparam name="TKey"></typeparam>
    public static class SqlUtility<TEntity, TKey> 
        where TEntity : class, IEntityObject<TKey>, new()
        where TKey : IComparable<TKey>, IEquatable<TKey> 
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="sp"></param>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        public static void ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string sp, object parameter, ISqlEntityDataMapper<TEntity, TKey> mapper)
        {
            ExecuteNonQuery(connection, transaction, sp, parameter, mapper, false);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="sp"></param>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        /// <param name="isForCreate"></param>
        /// <returns></returns>
		public static TKey ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string sp, object parameter, ISqlEntityDataMapper<TEntity, TKey> mapper, bool isForCreate)
        {
			var parameters = mapper.MapTo(sp, parameter, isForCreate);
        	return ExecuteNonQuery(connection, transaction, sp, parameters, mapper, CommandType.StoredProcedure, isForCreate);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="sp"></param>
        /// <param name="entity"></param>
        /// <param name="mapper"></param>
        public static void ExecuteNonQueryWithMapping(SqlConnection connection, SqlTransaction transaction, string sp, TEntity entity, ISqlEntityDataMapper<TEntity, TKey> mapper, bool isForCreate)
        {
            var parameters = mapper.MapTo(sp, entity, isForCreate);
            ExecuteNonQuery(connection, transaction, sp, parameters, mapper, CommandType.StoredProcedure, isForCreate);
            var outParams = new[]
                                {
                                    ParameterDirection.InputOutput, ParameterDirection.Output,
                                    ParameterDirection.ReturnValue
                                };
            var values =
                parameters.Where(p => outParams.Contains(p.Direction)).ToDictionary(p => p.ParameterName.Substring(1),
                                                                                    p => p.Value);
            mapper.MapFrom(sp, entity, values);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
		/// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <param name="mapper"></param>
        /// <param name="isForCreate"></param>
		/// <param name="commandType"></param>
        /// <returns></returns>
		public static TKey ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string commandText, IList<SqlParameter> parameters, ISqlEntityDataMapper<TEntity, TKey> mapper, CommandType commandType, bool isForCreate)
        {
            Guard.ArgumentNotNull(connection, "connection");
			Guard.ArgumentNotEmpty(commandText, "sp");
            Guard.ArgumentNotNull(parameters, "parameters");
            Guard.ArgumentNotNull(mapper, "mapper");

            var result = default(TKey);

			using (var sqlCommand = SetupCommand(connection, transaction, commandText, parameters, commandType))
            {
                try
                {
					if (commandType == CommandType.Text && isForCreate)
					{
                        using (var reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                result =
                                    (TKey)
                                    TypeInterpreter.GetValue(TypeInterpreter.GetTypeName(typeof (TKey)),
                                                             reader.GetValue(0).ToString(), null);
                            }
                            reader.Close();
                        }
					}
					else
					{
						sqlCommand.ExecuteNonQuery();
						if (isForCreate)
						{
							var keyParam = parameters.FirstOrDefault(p => (p.Direction == ParameterDirection.ReturnValue) || (p.Direction == ParameterDirection.Output));
							if (keyParam != null)
							{
								result = (TKey)keyParam.Value;
							}
							else
							{
								throw new ArgumentException(string.Format("Key for type {0} isn't found", typeof(TEntity)));
							}
						}                        
					}
                }
                catch (SqlException ex)
                {
                    var message = (sqlCommand.Parameters != null) && (sqlCommand.Parameters.Count > 0) ? string.Format("Error code: {0}. {1} Procedure {2} has got arguments: {3}", ex.Number, ex.Message, sqlCommand.CommandText, sqlCommand.Parameters.Cast<SqlParameter>().Select(p => p.ParameterName).Aggregate((next, working) => string.Format("{0}, {1}", next, working))) : ex.Message;
                    throw new SqlUtilityException(message, ex);
                }


            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="sp"></param>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        /// <returns></returns>
        public static IEnumerable<TEntity> ExecuteReader(SqlConnection connection, SqlTransaction transaction, string sp, object parameter, ISqlEntityDataMapper<TEntity, TKey> mapper)
        {
        	return ExecuteReader(connection, transaction, sp, parameter, mapper, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
		/// <param name="commandText"></param>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static IEnumerable<TEntity> ExecuteReader(SqlConnection connection, SqlTransaction transaction, string commandText, object parameter, ISqlEntityDataMapper<TEntity, TKey> mapper, CommandType commandType)
		{
			Guard.ArgumentNotNull(mapper, "mapper");

			var parameters = mapper.MapTo(commandText, parameter, false);
        	return ExecuteReader(connection, transaction, commandText, parameters, mapper, commandType);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		/// <param name="commandText"></param>
		/// <param name="parameters"></param>
		/// <param name="mapper"></param>
		/// <param name="commandType"></param>
		/// <returns></returns>
		public static IEnumerable<TEntity> ExecuteReader(SqlConnection connection, SqlTransaction transaction, string commandText, IList<SqlParameter> parameters, ISqlEntityDataMapper<TEntity, TKey> mapper, CommandType commandType)
		{
			Guard.ArgumentNotNull(connection, "connection");
			Guard.ArgumentNotEmpty(commandText, "commandText");
			Guard.ArgumentNotNull(mapper, "mapper");

			using (var reader = GetReader(connection, transaction, commandText, parameters, commandType))
			{
				while (reader.Read())
				{
					TEntity current;
					try
					{
						current = mapper.MapFrom(commandText, reader);
					}
					catch (SqlException ex)
					{
						var message = (parameters != null) && (parameters.Count > 0) ?
							string.Format("{0} Procedure {1} has got arguments: {2}",
								ex.Message,
								commandText,
								parameters
									.Select(p => p.ParameterName)
									.Aggregate((next, working) => string.Format("{0}, {1}", next, working))) :
							ex.Message;

						throw new SqlUtilityException(message, ex);
					}

					yield return current;
				}
			}
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="sp"></param>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        /// <returns></returns>
        public static TEntity ExecuteMultiReader(SqlConnection connection, SqlTransaction transaction, string sp, object parameter, ISqlEntityDataMapper<TEntity, TKey> mapper)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotEmpty(sp, "sp");
            Guard.ArgumentNotNull(mapper, "mapper");

            TEntity result = null;

            var parameters = mapper.MapTo(sp, parameter, false);

            using (var reader = GetReader(connection, transaction, sp, parameters))
            {
                int index = 0;
                do
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            try
                            {
                                result = mapper.MapFromMulti(sp, reader, result, index);
                            }
                            catch (SqlException ex)
                            {
                                string message = (parameters != null) && (parameters.Count > 0) ?
                                    string.Format("{0} Procedure {1} has got arguments: {2}",
                                        ex.Message,
                                        sp,
                                        parameters
                                            .Select(p => p.ParameterName)
                                            .Aggregate((next, working) => string.Format("{0}, {1}", next, working))) :
                                    ex.Message;

                                throw new SqlUtilityException(message, ex);
                            }
                        }
                    }

                    index++;
                }
                while (reader.NextResult());
            }

            return result;
        }

        private static SqlDataReader GetReader(SqlConnection connection, SqlTransaction tranaction, string sp, IList<SqlParameter> sqlParameters)
        {
        	return GetReader(connection, tranaction, sp, sqlParameters, CommandType.StoredProcedure);
        }

		private static SqlDataReader GetReader(SqlConnection connection, SqlTransaction tranaction, string commandText, IList<SqlParameter> sqlParameters, CommandType commandType)
		{
			using (var cmd = SetupCommand(connection, tranaction, commandText, sqlParameters, commandType))
			{
				return cmd.ExecuteReader();
			}
		}

		private static SqlCommand SetupCommand(SqlConnection connection, SqlTransaction tranaction, string commandText, IList<SqlParameter> sqlParameters, CommandType commandType)
        {
        	var sqlCommand = new SqlCommand
        	                 	{
									CommandType = commandType,
        	                 		Connection = connection,
        	                 		Transaction = tranaction
        	                 	};

			if (commandType == CommandType.Text && (sqlParameters != null))
			{
				foreach (var parameter in sqlParameters)
				{
					var parameterValues = parameter.Value as IEnumerable;

					if (parameterValues != null && !(parameterValues is string) && !(parameterValues is byte[]))
					{
						var parameterText = new List<string>();
						var i = 0;
						foreach (var value in parameterValues)
						{
							sqlCommand.Parameters.Add(new SqlParameter
							                          	{
							                          		ParameterName = string.Format("{0}_{1}", parameter.ParameterName, i),
							                          		Value = value
							                          	});
							parameterText.Add(string.Format("{0}_{1}", parameter.ParameterName, i));
							i++;
						}
						commandText = commandText.Replace(string.Format("{0}", parameter.ParameterName), string.Join(", ", parameterText));
					}
					else
					{
						sqlCommand.Parameters.Add(parameter);
					}
				}
			}
			else if ((sqlParameters != null) && sqlParameters.Count > 0)
			{
				sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
			}
			
			sqlCommand.CommandText = commandText;

            return sqlCommand;
        }
    }
}
